package db;

import java.sql.*;

import model.Product;

public class JDBCConnector {

	Connection con = null;

	public JDBCConnector() {

		// Kann ab Java 1.5 weggelassen werden
		// try{
		// Class.forName("org.postgresql.Driver");
		// } catch (ClassNotFoundException e){
		// e.printStackTrace();
		// }

		try {
			con = DriverManager.getConnection("jdbc:postgresql://java.is.uni-due.de/ws1011",
					"ws1011", "ftpw10");
			this.readDBState();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	// URL, Nutzer und Tabellen auslesen
	public void readDBState() {
		try {
			DatabaseMetaData metaData = con.getMetaData();
			String url = metaData.getURL();
			String user = metaData.getUserName();
			ResultSet tables = metaData.getTables(null, null, null, null);
			System.out.println("URL: " + url);
			System.out.println("USER: " + user);
			while (tables.next()) {
				String table = tables.getString("TABLE_NAME");
				System.out.println(" table: " + table);
			}
			tables.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private long insert(String name, double price, int quantity) {
		long id = -1;
		try (PreparedStatement pstm = con.prepareStatement(
				"INSERT INTO products(name, price, quantity) VALUES (?, ?, ?)",
				Statement.RETURN_GENERATED_KEYS);) {
			pstm.setString(1, name);
			pstm.setDouble(2, price);
			pstm.setInt(3, quantity);
			pstm.execute();
			ResultSet rs = pstm.getGeneratedKeys();
			if (rs.next()) {
				id = rs.getLong(1);
			}
			rs.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println("ID des gerade eingefügten Produkts: " + id);
		return id;
	}

	private void insert(Product product) {
		insert(product.getName(), product.getPrice(), product.getQuantity());
	}

	private Product read(long productID) {
		Product product = new Product();

		try (PreparedStatement pstm = con
				.prepareStatement("SELECT id, name, price, quantity FROM products WHERE id = ?")) {
			pstm.setLong(1, productID);
			try (ResultSet rs = pstm.executeQuery()) {
				if (rs.next()) {
					product.setId(rs.getLong("id"));
					product.setName(rs.getString("name"));
					product.setPrice(rs.getDouble("price"));
					product.setQuantity(rs.getInt("quantity"));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return product;
	}

	private void close() {
		try {
			// Verbindung schließen
			if (con != null) {
				con.close();
				System.out.println("Verbindung wurde geschlossen");
			}
		} catch (SQLException e) {
			System.out.println("Schließen der Verbindung fehlgeschlagen.");
			e.printStackTrace();
		}
	}

	// Methode zum Testen der Methoden insert(String name, double price, int quantity), insert(Product product),
	// read(long productID) und close()
	public static void main(String args[]) {
		JDBCConnector jdbcC = new JDBCConnector();

		// Insert testen
		Product testProduct = null;
		try {
			testProduct = new Product("Notebook", 599, 5);
		} catch (Exception e) {
			e.printStackTrace();
		}
		jdbcC.insert(testProduct);

		// Read testen
		Product productRead = jdbcC.read(000001);
		System.out.println(productRead.getId() + " " + productRead.getName() + " "
				+ productRead.getPrice() + " " + productRead.getQuantity());

		// Close
		jdbcC.close();
	}

}
